package bainuo

import groovy.sql.Sql

def execute() {
    def sqlServer = null
    def mySql = null
    try {
        // 创建数据库连接
        sqlServer = groovy.sql.Sql.newInstance(mypara.hqcon(2))
        // 连接数据库...
        mySql = Sql.newInstance("jdbc:mysql://10.1.1.209:3306/sqlserver", "oper_dc", "operzADF2020qwer", "com.mysql.cj.jdbc.Driver")

        syncViewToMySQL("procurement_and_payment_info",sqlServer,mySql)
    } catch (Exception e) {
        e.printStackTrace()
    } finally {
        if (sqlServer != null) {
            sqlServer.close()
        }
        if (mySql != null) {
            mySql.close()
        }
    }
}
def syncViewToMySQL(viewName, def sqlServer, def mySql) {
    // 开启事务
    mySql.withTransaction { status ->
        try {
            // Step 1: 获取 SQL Server 视图的定义
            def createTableSQL = getCreateTableSQLFromSQLServer(viewName, sqlServer)

            if (createTableSQL) {
                // 检查是否有数据需要同步
                if (!hasDataToSync(viewName, sqlServer)) {
                    println "No data found in SQL Server view '$viewName'. Transaction rolled back."
                    throw new RuntimeException("No data to sync.")
                }

                // 删除表
                def truncateSql = "DROP TABLE " + viewName
                mySql.execute(truncateSql)

                // Step 2: 在 MySQL 中创建表
                println "Creating table in MySQL from SQL Server view '$viewName'..."
                mySql.execute(createTableSQL)

                // Step 3: 同步视图数据到 MySQL
                println "Syncing data from SQL Server view '$viewName' to MySQL..."
                syncData(viewName, sqlServer, mySql)

                // 提交事务（默认行为）
            } else {
                println "Failed to get view definition from SQL Server."
                status.rollback()
            }
        } catch (Exception e) {
            // 回滚事务并打印错误信息
            status.rollback()
            throw e
        }
    }
}

def hasDataToSync(viewName, def sqlServer) {
    // 检查 SQL Server 视图中是否有数据
    def selectSQL = "SELECT TOP 1 1 FROM "+viewName+" WHERE 1=1 "
    return !sqlServer.rows(selectSQL).isEmpty()
}

def getCreateTableSQLFromSQLServer(viewName,sqlServer) {
    // 从 SQL Server 获取视图的列定义
    def query = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$viewName'"
    def columns = []

    sqlServer.eachRow(query) { row ->
        columns << [name: row.COLUMN_NAME, type: mapSQLServerTypeToMySQL(row.DATA_TYPE)]
    }

    if (columns.isEmpty()) {
        println "No columns found for view '$viewName'."
        return null
    }

    // 构建 MySQL 表的创建语句
    def createTableSQL = "CREATE TABLE IF NOT EXISTS "+ viewName +" (\n"
    columns.eachWithIndex { column, i ->
        // 确保列名用反引号包裹
        createTableSQL += "  `${column.name}` ${column.type}"
        if (i < columns.size() - 1) {
            createTableSQL += ",\n"
        }
    }
    createTableSQL += "\n);"

    println(createTableSQL)

    return createTableSQL
}

def mapSQLServerTypeToMySQL(sqlServerType) {
    // 简单的映射 SQL Server 数据类型到 MySQL 数据类型
    switch (sqlServerType.toLowerCase()) {
        case 'nvarchar': return 'VARCHAR(255)'
        case 'varchar': return 'VARCHAR(255)'
        case 'int': return 'INT'
        case 'bigint': return 'BIGINT'
        case 'float': return 'FLOAT'
        case 'datetime': return 'DATETIME'
        case 'bit': return 'BOOLEAN'
        case 'decimal': return 'DECIMAL(18, 2)'
        case 'text': return 'TEXT'
        default: return 'VARCHAR(255)'
    }
}



def syncData(viewName,sqlServer,mySql) {
    // 从 SQL Server 获取视图数据
    def selectSQL = "SELECT * FROM "+viewName
    def rows = sqlServer.rows(selectSQL)

    // 将数据插入到 MySQL 中
    def insertSQL = buildInsertSQL(viewName, rows[0].keySet())
    rows.each { row ->
        def values = row.collect { it.value }
        mySql.execute(insertSQL, values)
    }
}

def buildInsertSQL(viewName, columns) {
    def columnNames = columns.join(', ')
    def placeholders = columns.collect { '?' }.join(', ')
    return "INSERT INTO $viewName ($columnNames) VALUES ($placeholders)"
}


